/********************************************/
  // Creation History
  // --------------------------------------------------------------
  //	Company:			HTHK
  //	NAME:				TimeSheetSearch.java
  //    Title   :			find TimeSheet Records, return one or more records
  //	FUNCTION:
  //	BY:					Jet Tao
  //	DATE:				09/12/2003
  // UPDATE BY:			Beyes Xia
  // UPDATE DATE:			2004-1-10 13:55
  // LAST UPDATE DATE:		2004-1-12 17:15
  // --------------------------------------------------------------
/*********************************************/

package tss.timesheet;

import javax.servlet.*;
import javax.servlet.http.*;

import org.apache.log4j.Logger;

import java.io.*;
import java.util.*;
import java.sql.*;

import com.ffpc.sql.*;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

import tss.common.DBServlet2;

/**
 * A Class class.
 * <P>
 * @author HTHK
 */
public class TimeSheetSearch
  extends DBServlet2 {
	private Logger logger = Logger.getLogger(TimeSheetSearch.class);
  @Override
protected String perform(  HttpServletRequest request,
                             HttpServletResponse response,
                             StrutsMessage message)
    throws  ServletException,
            IOException,
            SQLException,
            DataException,
            Exception
  {
	  String staff_no=request.getParameter("staff_name_option");
	  String which_week=request.getParameter("which_week");
	  String grade=null;
//****** Add By Beyes Xia
	  String staff_name = "";
//******
	  String start_date=null;
	  String search_date=request.getParameter("selected_date");
	  logger.debug("selected_date="+search_date);
	  logger.debug("staff_no="+staff_no);
	  ResultSet rs=null;
	  Statement stmt=null;
	  Vector v_time_sheet=new Vector();
	  String[] a_week=new String[7];
	  int add_num=0;
	  int counter=0;
	  /********add  try{}catch{}finally{}  2013-07-05 by leosen******************/
	  try{
		//****** Add By Beyes Xia
			HttpSession session = request.getSession();
			session.setAttribute("staffNameDfuValue",staff_no);
	//*****
		  if(which_week!=null)
		  {
			  if(which_week.equals("0"))//last week;
				  add_num=7;
			  else if(which_week.equals("2"))//next week;
				  add_num=-7;
		  }
		  String sql=" Select grade,project_name,job_name,"+//3
					 " sunday_spend,monday_spend,tuesday_spend,"+//3
					 " wednesday_spend,thursday_spend,"+//2
					 " friday_spend,saturday_spend,to_char(start_date,'YYYY-MM-DD'),"+//3
	//******	Add By Beyes Xia
					"overtime_ind"+
	//******							 
					 " from tss.tss_t_timesheet"+
					 " where staff_no='"+staff_no+"'"+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date>="+add_num+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date<"+(add_num+7);
		logger.debug("search_date="+search_date);
		logger.debug("add_num="+add_num);
		logger.debug("sql="+sql);
	//****** Add By Beyes Xia
		String sql_staffName = " Select alias,surname"+
				 " from TSS.TSS_C_EMPLOYEE"+
				 " where staff_no='"+staff_no+"'";
	//******
		 
		  stmt=this.getConnection().createStatement();
		  rs=stmt.executeQuery(sql);
		 
		  while(rs.next())
		  {
		 	  counter++;
			  grade=rs.getString(1);
			  start_date=rs.getString(11);
			  String[] tmp_timesheet=new String[10];
			  tmp_timesheet[0]=rs.getString(2);
			  tmp_timesheet[1]=rs.getString(3);

			  tmp_timesheet[2]=(rs.getString(4)==null)?"":rs.getString(4);
			  tmp_timesheet[3]=(rs.getString(5)==null)?"":rs.getString(5);
			  tmp_timesheet[4]=(rs.getString(6)==null)?"":rs.getString(6);
			  tmp_timesheet[5]=(rs.getString(7)==null)?"":rs.getString(7);
			  tmp_timesheet[6]=(rs.getString(8)==null)?"":rs.getString(8);
			  tmp_timesheet[7]=(rs.getString(9)==null)?"":rs.getString(9);
			  tmp_timesheet[8]=(rs.getString(10)==null)?"":rs.getString(10);
	//****** Add By Beyes Xia
			  tmp_timesheet[9]=rs.getString(12);
	//******
			  v_time_sheet.add(tmp_timesheet);
//			  logger.debug("in java:"+rs.getString(4)+"-"+rs.getString(5)+"-"+rs.getString(6)+"-"+rs.getString(7)+"-"+rs.getString(8)+"-"+rs.getString(9)+"-"+rs.getString(10));
		  }
		logger.debug("Counter="+counter);
		  String sql_last=" Select grade"+
					 " from tss.tss_t_timesheet"+
					 " where staff_no='"+staff_no+"'"+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date>="+(add_num+7)+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date<"+(add_num+14);
		  String sql_next=" Select grade"+
					 " from tss.tss_t_timesheet"+
					 " where staff_no='"+staff_no+"'"+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date>="+(add_num-7)+
					 " and to_date('"+search_date+"','YYYY-MM-DD')-start_date<"+add_num;
		  String sql_get_week=" Select '"+start_date+"',"+
							" to_char(to_date('"+start_date+"','YYYY-MM-DD')+1,'YYYY-MM-DD'),"+
							" to_char(to_date('"+start_date+"','YYYY-MM-DD')+2,'YYYY-MM-DD'),"+
							" to_char(to_date('"+start_date+"','YYYY-MM-DD')+3,'YYYY-MM-DD'),"+
							" to_char(to_date('"+start_date+"','YYYY-MM-DD')+4,'YYYY-MM-DD'),"+
							" to_char(to_date('"+start_date+"','YYYY-MM-DD')+5,'YYYY-MM-DD'),"+
							" to_char(to_date('"+start_date+"','YYYY-MM-DD')+6,'YYYY-MM-DD')"+
							" from dual";
	 
		 
		  rs=stmt.executeQuery(sql_last);
		  if(rs.next())
		  {
	          request.setAttribute("last_flag","false");
//			  logger.debug("last is false");
		  }
		  else
	      {
			  request.setAttribute("last_flag","true");
//			  logger.debug("last is true");
		  }

		  rs=stmt.executeQuery(sql_next);

		  if(rs.next())
		  {
	          request.setAttribute("next_flag","false");
//			  logger.debug("next is false");
		  }
		  else
		  {
	          request.setAttribute("next_flag","true");
//			  logger.debug("next is true");
		  }
		  if(start_date!=null)
		  {
			  rs=stmt.executeQuery(sql_get_week);

			  if(rs.next())
			  {
				  a_week[0]=rs.getString(1);
				  a_week[1]=rs.getString(2);
				  a_week[2]=rs.getString(3);
				  a_week[3]=rs.getString(4);
				  a_week[4]=rs.getString(5);
				  a_week[5]=rs.getString(6);
				  a_week[6]=rs.getString(7);
	/*
				  logger.debug(a_week[0]);
				  logger.debug(a_week[1]);
				  logger.debug(a_week[2]);
				  logger.debug(a_week[3]);
				  logger.debug(a_week[4]);
				  logger.debug(a_week[5]);
				  logger.debug(a_week[6]);
	*/
			}
		  }
	//****** Add By Beyes Xia
		rs=stmt.executeQuery(sql_staffName);
		if(rs.next())
		{
			staff_name = rs.getString(1)+" "+rs.getString(2);
		}
	  } finally{
		  JdbcUtil.release(stmt, rs);
	  }

//	  if(counter==0 && which_week==null)
	  if(counter==0)
      {
          message.setMessage("No record, please input another criteria.");
          return "tss/timesheet/TimeSheetSearch.jsp";
      }
      else
      {
          request.setAttribute("TimeSheet",v_time_sheet);
          request.setAttribute("Grade",grade);
          request.setAttribute("StartDate",start_date);
          request.setAttribute("StaffNo",staff_no);
          request.setAttribute("AWeek",a_week);
//****** Add By Beyes Xia
		request.setAttribute("StaffName",staff_name);
//******
          return "tss/timesheet/TimeSheetView.jsp";
      }
  }
}


